package com.asistencia.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.asistencia.dao.interfaces.EnrollmentDao;
import com.asistencia.domain.Course;
import com.asistencia.domain.Enrollment;
import com.asistencia.domain.Semester;
import com.asistencia.domain.Student;

@Repository
@SuppressWarnings({ "rawtypes", "unchecked" })
public class EnrollmentJdbc implements EnrollmentDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public List<Enrollment> listByCourseSemester(Integer idCourse,	Integer idSemester) throws DataAccessException {
		String SQL="SELECT * FROM enrollment WHERE idCourse =? AND idSemester = ? ";
		List matches=jdbcTemplate.query(SQL, new Object[]{idCourse,idSemester }, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Enrollment enr= new Enrollment();
				Semester sem= new Semester();
				Course co=new Course();
				Student st=new Student();
				
				enr.setIdEnrollment(rs.getInt("idenrollment"));
				enr.setEnrollmentDate(rs.getString("enrollment_date"));
				sem.setIdSemester(rs.getInt("idsemester"));
				st.setIdStudent(rs.getInt("idstudent"));
				co.setIdCourse(rs.getInt("idcourse"));
				
				enr.setMySemester(sem);
				enr.setMyStudent(st);
				enr.setMyCourse(co);
				
				return enr;
			}});
		return (matches.size()>0 ? (List<Enrollment>)matches: null);
		}

	@Override
	public List<Enrollment> listByStudentSemester(Integer idStudent,
			Integer idSemester) throws DataAccessException {
		String SQL="SELECT * FROM enrollment WHERE idstudent = ? AND idsemester = ? ";
		List matches=jdbcTemplate.query(SQL, new Object[]{idStudent,idSemester }, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Enrollment enr= new Enrollment();
				Semester sem= new Semester();
				Course co=new Course();
				Student st=new Student();
				
				enr.setIdEnrollment(rs.getInt("idenrollment"));
				enr.setEnrollmentDate(rs.getString("enrollment_date"));
				sem.setIdSemester(rs.getInt("idsemester"));
				st.setIdStudent(rs.getInt("idstudent"));
				co.setIdCourse(rs.getInt("idcourse"));
				
				enr.setMySemester(sem);
				enr.setMyStudent(st);
				enr.setMyCourse(co);
				
				return enr;
			}});
		return (matches.size()>0 ? (List<Enrollment>)matches: null);
		}
	
	@Override
	public List<Enrollment> listByStudent(Integer idStudent)
			throws DataAccessException {

			String SQL="SELECT * FROM enrollment WHERE idstudent = ? ";
			List matches=jdbcTemplate.query(SQL, new Object[]{idStudent}, new RowMapper(){
				@Override
				public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
					Enrollment enr= new Enrollment();
					Semester sem= new Semester();
					Course co=new Course();
					Student st=new Student();
					
					enr.setIdEnrollment(rs.getInt("idenrollment"));
					enr.setEnrollmentDate(rs.getString("enrollment_date"));
					sem.setIdSemester(rs.getInt("idsemester"));
					st.setIdStudent(rs.getInt("idstudent"));
					co.setIdCourse(rs.getInt("idcourse"));
					
					enr.setMySemester(sem);
					enr.setMyStudent(st);
					enr.setMyCourse(co);
					
					return enr;
				}});
			return (matches.size()>0 ? (List<Enrollment>)matches: null);
			}
		

	@Override
	public List<Enrollment> listByDate(String date) throws DataAccessException {

		String SQL="SELECT * FROM enrollment WHERE enrollment_date LIKE ? ";
		List matches=jdbcTemplate.query(SQL, new Object[]{date}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Enrollment enr= new Enrollment();
				Semester sem= new Semester();
				Course co=new Course();
				Student st=new Student();
				
				enr.setIdEnrollment(rs.getInt("idenrollment"));
				enr.setEnrollmentDate(rs.getString("enrollment_date"));
				sem.setIdSemester(rs.getInt("idsemester"));
				st.setIdStudent(rs.getInt("idstudent"));
				co.setIdCourse(rs.getInt("idcourse"));
				
				enr.setMySemester(sem);
				enr.setMyStudent(st);
				enr.setMyCourse(co);
				
				return enr;
			}});
		return (matches.size()>0 ? (List<Enrollment>)matches: null);
		}
	
	@Override
	public List<Enrollment> listBetweenDates(String date, String date1)
			throws DataAccessException {
		String SQL="SELECT * FROM enrollment WHERE enrollment_date LIKE ? AND date1 LIKE =? ";
		List matches=jdbcTemplate.query(SQL, new Object[]{date, date1}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Enrollment enr= new Enrollment();
				Semester sem= new Semester();
				Course co=new Course();
				Student st=new Student();
				
				enr.setIdEnrollment(rs.getInt("idenrollment"));
				enr.setEnrollmentDate(rs.getString("enrollment_date"));
				sem.setIdSemester(rs.getInt("idsemester"));
				st.setIdStudent(rs.getInt("idstudent"));
				co.setIdCourse(rs.getInt("idcourse"));
				
				enr.setMySemester(sem);
				enr.setMyStudent(st);
				enr.setMyCourse(co);
				
				return enr;
			}});
		return (matches.size()>0 ? (List<Enrollment>)matches: null);
		}

	@Override
	public Enrollment getById(Integer idEnrollment) throws DataAccessException {
		String SQL="SELECT * FROM enrollment WHERE idenrollment = ?";
		List matches =jdbcTemplate.query(SQL, new Object[]{idEnrollment}, new RowMapper(){
			
			@Override
				public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
				Enrollment enr= new Enrollment();
				Semester sem= new Semester();
				Course co=new Course();
				Student st=new Student();
				
				enr.setIdEnrollment(rs.getInt("idenrollment"));
				enr.setEnrollmentDate(rs.getString("enrollment_date"));
				sem.setIdSemester(rs.getInt("idsemester"));
				st.setIdStudent(rs.getInt("idstudent"));
				co.setIdCourse(rs.getInt("idcourse"));
				
				enr.setMySemester(sem);
				enr.setMyStudent(st);
				enr.setMyCourse(co);
				
				return enr;
						
			}});
		return (matches.size()>0 ? (Enrollment)matches: null);
		}
}
